﻿using DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAO
{
    public class ThongSoChungDAO
    {
/*-------------------------LOAD----------------------------------------------------
CREATE PROC LOADTHONGSO
CREATE PROC LOADTHONGSO_MATS @MaTS int
CREATE PROC LOADTHONGSO_MATB @MaTB char(20)
 */

        public DataTable LoadThongSo()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADTHONGSO", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }
        public DataTable LoadThongSo_MaTS(int MaTS)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADTHONGSO_MATS", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;
            _da.SelectCommand.Parameters.Add("@MaTS", SqlDbType.Int).Value = MaTS;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }
        public DataTable LoadThongSo_MaTB(string MaTB)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADTHONGSO_MATB", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;
            _da.SelectCommand.Parameters.Add("@MaTB", SqlDbType.Char,20).Value = MaTB;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }

/*-------------------------INSERT----------------------------------------------------       
THEMTHONGSO @MaTS int OUT,@MaTB char(20),@TenTB nvarchar(50),@NgayNhap datetime,@CSKhaDung int,@DonGia int
 */
        //Thêm thông số rồi return ra MaTS thêm vào
        public int ThemThongSo(ThongSoChungDTO ts)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("THEMTHONGSO", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@MaTS", SqlDbType.Int));
            cmd.Parameters["@MaTS"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add(new SqlParameter("@MaTB", SqlDbType.Char,20));
            cmd.Parameters.Add(new SqlParameter("@TenTB", SqlDbType.NVarChar,50));
            cmd.Parameters.Add(new SqlParameter("@NgayNhap", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@CSKhaDung", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@DonGia", SqlDbType.Int));
            cmd.Parameters["@MaTS"].Value = ts.MATS;
            cmd.Parameters["@MaTB"].Value = ts.MATB;
            cmd.Parameters["@TenTB"].Value = ts.TENTB;
            cmd.Parameters["@NgayNhap"].Value = ts.NGAYNHAP;
            cmd.Parameters["@CSKhaDung"].Value = ts.CSKHADUNG;
            cmd.Parameters["@DonGia"].Value = ts.DONGIA;
            cmd.ExecuteNonQuery();
            int _kq = (int)cmd.Parameters["@MaTS"].Value;
            _cn.Close();
            return _kq;
        }



        
/*-------------------------UPDATE----------------------------------------------------
SUATHONGSO @MaTS int,@MaTB char(20),@TenTB nvarchar(50),@NgayNhap datetime,@CSKhaDung int,@DonGia int
*/
        //Trả về 1 nếu thành công, 0 ngược lại
        public int SuaThongSo(ThongSoChungDTO ts)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("SUATHONGSO", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@MaTS", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@MaTB", SqlDbType.Char, 20));
            cmd.Parameters.Add(new SqlParameter("@TenTB", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@NgayNhap", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@CSKhaDung", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@DonGia", SqlDbType.Int));
            cmd.Parameters["@MaTS"].Value = ts.MATS;
            cmd.Parameters["@MaTB"].Value = ts.MATB;
            cmd.Parameters["@TenTB"].Value = ts.TENTB;
            cmd.Parameters["@NgayNhap"].Value = ts.NGAYNHAP;
            cmd.Parameters["@CSKhaDung"].Value = ts.CSKHADUNG;
            cmd.Parameters["@DonGia"].Value = ts.DONGIA;
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }



       


/*-------------------------DELETE----------------------------------------------------
 XOATHONGSO @MaTS int
 */
        //Trả về 1 nếu thành công, 0 ngược lại
        public int XoaThongSo(int MATS)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("XOATHONGSO", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@MaTS", SqlDbType.Int));
            cmd.Parameters["@MaTS"].Value = MATS;
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }
    }
}
